using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.Look.Imagem.Entidades;

namespace MakeIT.Look.Imagem.Negocios
{
	public partial class LookDAOT : DAOT
	{

		public PlaylistitemVO getPlaylistitem(int? idPlayListItem)
		{
			PlaylistitemVO playlistitemVO = null;
			List<PlaylistitemVO> listPlaylistitemVOs = getPlaylistitems(idPlayListItem, null, null, null, null, null, null, null, null);
			if (listPlaylistitemVOs != null && listPlaylistitemVOs.Count > 0)
			{
				playlistitemVO = listPlaylistitemVOs[0];
			}
			return playlistitemVO;
		}

		public List<PlaylistitemVO> getPlaylistitems()
		{
			return getPlaylistitems(null, null, null, null, null, null, null, null, null);
		}

		public List<PlaylistitemVO> getPlaylistitems(int? idPlayListItem)
		{
			return getPlaylistitems(idPlayListItem, null, null, null, null, null, null, null, null);
		}

		public List<PlaylistitemVO> getPlaylistitems(int? idPlayListItem, int? idPlayList, bool? home, bool? staff, bool? ensaio, bool? evento, bool? album, bool? contato, bool? ativo)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT pli.home as pli_home, pli.staff as pli_staff, pli.ensaio as pli_ensaio, pli.evento as pli_evento, pli.album as pli_album, pli.contato as pli_contato, pli.idPlayListItem as pli_idPlayListItem, pli.idPlayList as pli_idPlayList, Pll.playlist as Pll_playlist, pli.musicaMime as pli_musicaMime, pli.musicaNome as pli_musicaNome , pli.musicaTamanho as pli_musicaTamanho , pli.ativo as pli_ativo, pli.cadastro as pli_cadastro, pli.cadastradoPor as pli_cadastradoPor, pli.edicao as pli_edicao, pli.editadoPor as pli_editadoPor  ");
			query.AppendLine("FROM playlistitem as pli LEFT OUTER JOIN playlist as Pll on pli.idPlayList=Pll.idPlayList  ");
			if (home != null)
			{
				query.AppendLine(where + "pli.home=@home ");
				where = "AND ";
			}
			if (staff != null)
			{
				query.AppendLine(where + "pli.staff=@staff ");
				where = "AND ";
			}
			if (ensaio != null)
			{
				query.AppendLine(where + "pli.ensaio=@ensaio ");
				where = "AND ";
			}
			if (evento != null)
			{
				query.AppendLine(where + "pli.evento=@evento ");
				where = "AND ";
			}
			if (album != null)
			{
				query.AppendLine(where + "pli.album=@album ");
				where = "AND ";
			}
			if (contato != null)
			{
				query.AppendLine(where + "pli.contato=@contato ");
				where = "AND ";
			}
			if (idPlayListItem != null)
			{
				query.AppendLine(where + "pli.idPlayListItem=@idPlayListItem ");
				where = "AND ";
			}
			if (idPlayList != null)
			{
				query.AppendLine(where + "pli.idPlayList=@idPlayList ");
				where = "AND ";
			}
			if (ativo != null)
			{
				query.AppendLine(where + "pli.ativo=@ativo ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY pli.idPlayListItem ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (home!= null) cmd.Parameters.Add(getParameter(cmd, "@home", home.Value?"S":"N"));
			if (staff!= null) cmd.Parameters.Add(getParameter(cmd, "@staff", staff.Value?"S":"N"));
			if (ensaio!= null) cmd.Parameters.Add(getParameter(cmd, "@ensaio", ensaio.Value?"S":"N"));
			if (evento!= null) cmd.Parameters.Add(getParameter(cmd, "@evento", evento.Value?"S":"N"));
			if (album!= null) cmd.Parameters.Add(getParameter(cmd, "@album", album.Value?"S":"N"));
			if (contato!= null) cmd.Parameters.Add(getParameter(cmd, "@contato", contato.Value?"S":"N"));
			if (idPlayListItem!= null) cmd.Parameters.Add(getParameter(cmd, "@idPlayListItem", idPlayListItem));
			if (idPlayList!= null) cmd.Parameters.Add(getParameter(cmd, "@idPlayList", idPlayList));
			if (ativo!= null) cmd.Parameters.Add(getParameter(cmd, "@ativo", ativo.Value?"S":"N"));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<PlaylistitemVO> listPlaylistitem = new List<PlaylistitemVO>();

				while (reader.Read())
				{
					PlaylistitemVO playlistitemVO = PlaylistitemVO.CreateVO();
					playlistitemVO.home = reader.GetString(reader.GetOrdinal("pli_home")).Equals("S");
					playlistitemVO.staff = reader.GetString(reader.GetOrdinal("pli_staff")).Equals("S");
					playlistitemVO.ensaio = reader.GetString(reader.GetOrdinal("pli_ensaio")).Equals("S");
					playlistitemVO.evento = reader.GetString(reader.GetOrdinal("pli_evento")).Equals("S");
					playlistitemVO.album = reader.GetString(reader.GetOrdinal("pli_album")).Equals("S");
					playlistitemVO.contato = reader.GetString(reader.GetOrdinal("pli_contato")).Equals("S");
					playlistitemVO.idPlayListItem = reader.GetInt32(reader.GetOrdinal("pli_idPlayListItem"));
					playlistitemVO.idPlayList = reader.GetInt32(reader.GetOrdinal("pli_idPlayList"));
					if (!reader.IsDBNull(reader.GetOrdinal("Pll_playlist")))
						playlistitemVO.idPlayListVO.playlist = reader.GetString(reader.GetOrdinal("Pll_playlist"));
					if (!reader.IsDBNull(reader.GetOrdinal("pli_musicaMime")))
						playlistitemVO.musicaMime = reader.GetString(reader.GetOrdinal("pli_musicaMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("pli_musicaNome")))
						playlistitemVO.musicaNome = reader.GetString(reader.GetOrdinal("pli_musicaNome"));
					if (!reader.IsDBNull(reader.GetOrdinal("pli_musicaTamanho")))
						playlistitemVO.musicaTamanho = reader.GetInt32(reader.GetOrdinal("pli_musicaTamanho"));
					playlistitemVO.ativo = reader.GetString(reader.GetOrdinal("pli_ativo")).Equals("S");
					playlistitemVO.cadastro = reader.GetDateTime(reader.GetOrdinal("pli_cadastro"));
                    //playlistitemVO.cadastradoPor = reader.GetString(reader.GetOrdinal("pli_cadastradoPor"));
                    playlistitemVO.cadastradoPor = reader["pli_cadastradoPor"] as string;
					playlistitemVO.edicao = reader.GetDateTime(reader.GetOrdinal("pli_edicao"));
					playlistitemVO.editadoPor = reader.GetString(reader.GetOrdinal("pli_editadoPor"));


					listPlaylistitem.Add(playlistitemVO);
				}
				reader.Close();
				return listPlaylistitem;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setPlaylistitem(PlaylistitemVO playlistitemVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE playlistitem ");
			query.AppendLine("SET home=@home, staff=@staff, ensaio=@ensaio, evento=@evento, album=@album, contato=@contato, idPlayList=@idPlayList, musicaMime=@musicaMime, musicaNome=@musicaNome, musicaTamanho=@musicaTamanho, ativo=@ativo,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idPlayListItem=@idPlayListItem ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@home", (playlistitemVO.home==null?" ":(playlistitemVO.home.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@staff", (playlistitemVO.staff==null?" ":(playlistitemVO.staff.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@ensaio", (playlistitemVO.ensaio==null?" ":(playlistitemVO.ensaio.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@evento", (playlistitemVO.evento==null?" ":(playlistitemVO.evento.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@album", (playlistitemVO.album==null?" ":(playlistitemVO.album.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@contato", (playlistitemVO.contato==null?" ":(playlistitemVO.contato.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@idPlayListItem", playlistitemVO.idPlayListItem));
			cmd.Parameters.Add(getParameter(cmd, "@idPlayList", playlistitemVO.idPlayList));
			cmd.Parameters.Add(getParameter(cmd, "@musicaMime", playlistitemVO.musicaMime));
			cmd.Parameters.Add(getParameter(cmd, "@musicaNome", playlistitemVO.musicaNome));
			cmd.Parameters.Add(getParameter(cmd, "@musicaTamanho", playlistitemVO.musicaTamanho));
			cmd.Parameters.Add(getParameter(cmd, "@ativo", (playlistitemVO.ativo==null?" ":(playlistitemVO.ativo.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", playlistitemVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", playlistitemVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", playlistitemVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", playlistitemVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newPlaylistitem(PlaylistitemVO playlistitemVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO playlistitem ");
			query.AppendLine("  ( home, staff, ensaio, evento, album, contato, idPlayList, musicaMime, musicaNome, musicaTamanho, ativo, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @home, @staff, @ensaio, @evento, @album, @contato, @idPlayList, @musicaMime, @musicaNome, @musicaTamanho, @ativo, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@home", (playlistitemVO.home==null?" ":(playlistitemVO.home.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@staff", (playlistitemVO.staff==null?" ":(playlistitemVO.staff.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@ensaio", (playlistitemVO.ensaio==null?" ":(playlistitemVO.ensaio.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@evento", (playlistitemVO.evento==null?" ":(playlistitemVO.evento.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@album", (playlistitemVO.album==null?" ":(playlistitemVO.album.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@contato", (playlistitemVO.contato==null?" ":(playlistitemVO.contato.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@idPlayList", playlistitemVO.idPlayList));
			cmd.Parameters.Add(getParameter(cmd, "@musicaMime", playlistitemVO.musicaMime));
			cmd.Parameters.Add(getParameter(cmd, "@musicaNome", playlistitemVO.musicaNome));
			cmd.Parameters.Add(getParameter(cmd, "@musicaTamanho", playlistitemVO.musicaTamanho));
			cmd.Parameters.Add(getParameter(cmd, "@ativo", (playlistitemVO.ativo==null?" ":(playlistitemVO.ativo.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", playlistitemVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", playlistitemVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", playlistitemVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", playlistitemVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('playlistitem') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delPlaylistitem(PlaylistitemVO playlistitemVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM playlistitem ");
			query.AppendLine("WHERE idPlayListItem=@idPlayListItem ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idPlayListItem", playlistitemVO.idPlayListItem));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
		public int getCountPlaylistitemxIdPlayList(int? idPlayList)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idPlayList) as ocorrencias FROM playlistitem WHERE IdPlayList=@IdPlayList ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdPlayList", idPlayList));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}
		public void setPlaylistitemMusica(PlaylistitemVO playlistitemVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE playlistitem ");
			query.AppendLine("SET musica=@musica, musicaMime=@musicaMime, musicaNome=@musicaNome, musicaTamanho=@musicaTamanho ");
			query.AppendLine("WHERE idPlayListItem=@idPlayListItem ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idPlayListItem", playlistitemVO.idPlayListItem));

			cmd.Parameters.Add(getParameter(cmd, "@musica", playlistitemVO.musica));
			cmd.Parameters.Add(getParameter(cmd, "@musicaMime", playlistitemVO.musicaMime));
			cmd.Parameters.Add(getParameter(cmd, "@musicaNome", playlistitemVO.musicaNome));
			cmd.Parameters.Add(getParameter(cmd, "@musicaTamanho", playlistitemVO.musicaTamanho));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public void getPlaylistitemMusica(PlaylistitemVO playlistitemVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT musica as pli_musica, musicaMime as pli_musicaMime, musicaNome as pli_musicaNome, musicaTamanho as pli_musicaTamanho ");
			query.AppendLine("FROM playlistitem ");
			query.AppendLine("WHERE idPlayListItem=@idPlayListItem ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idPlayListItem", playlistitemVO.idPlayListItem));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				if (reader.Read())
				{
					if (!reader.IsDBNull(reader.GetOrdinal("pli_musica")))
						playlistitemVO.musica = getBytesFromBlob(reader, "pli_musica");

					if (!reader.IsDBNull(reader.GetOrdinal("pli_musicaMime")))
						playlistitemVO.musicaMime = reader.GetString(reader.GetOrdinal("pli_musicaMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("pli_musicaNome")))
						playlistitemVO.musicaNome = reader.GetString(reader.GetOrdinal("pli_musicaNome"));
					if (!reader.IsDBNull(reader.GetOrdinal("pli_musicaTamanho")))
						playlistitemVO.musicaTamanho = reader.GetInt32(reader.GetOrdinal("pli_musicaTamanho"));

				}
				reader.Close();
				return;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

	}
}
